"""
Donors API routes - Donor Management
Using raw SQL queries with psycopg2 (NO ORM!)
"""

from flask import Blueprint, request, jsonify
from db.connection import execute_query

bp = Blueprint('donors', __name__, url_prefix='/api/donors')

@bp.route('', methods=['GET'])
def get_donors():
    """
    Get all donors with optional filtering
    GET /api/donors?keyword=xxx&blood_type=A&is_active=true&page=1&pageSize=10
    """
    try:
        keyword = request.args.get('keyword', '').strip()
        blood_type = request.args.get('blood_type', '').strip()
        is_active = request.args.get('is_active', '').strip()
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('pageSize', 10))
        
        query = """
            SELECT d.donor_id, d.donor_code, d.full_name, d.date_of_birth,
                   d.ethnicity, d.marital_status, d.blood_type, d.height_cm, d.weight_kg,
                   d.eye_color, d.hair_color, d.education_level, d.occupation,
                   d.contact_email, d.contact_phone, d.is_active, d.registration_date,
                   -- 只有无后代记录且样本未被订单使用的捐精人才能删除
                   (NOT EXISTS(SELECT 1 FROM offspring_tracking WHERE donor_id = d.donor_id)
                    AND NOT EXISTS(
                        SELECT 1 FROM sperm_vials sv
                        JOIN order_items oi ON sv.vial_id = oi.vial_id
                        WHERE sv.donor_id = d.donor_id
                    )) as can_delete
            FROM donors d
            WHERE 1=1
        """
        
        params = []
        
        # 关键词搜索（姓名或代码）
        if keyword:
            query += " AND (d.full_name ILIKE %s OR d.donor_code ILIKE %s)"
            search_pattern = f'%{keyword}%'
            params.extend([search_pattern, search_pattern])
        
        # 血型筛选
        if blood_type:
            query += " AND d.blood_type = %s"
            params.append(blood_type)
        
        # 状态筛选
        if is_active:
            is_active_bool = is_active.lower() in ['true', '1', 'yes']
            query += " AND d.is_active = %s"
            params.append(is_active_bool)
        
        query += " ORDER BY d.donor_id ASC"
        
        # 分页
        offset = (page - 1) * page_size
        query += " LIMIT %s OFFSET %s"
        params.extend([page_size, offset])
        
        donors = execute_query(query, tuple(params) if params else None)
        
        # 获取总数
        count_query = """
            SELECT COUNT(*) as count
            FROM donors
            WHERE 1=1
        """
        count_params = []
        
        if keyword:
            count_query += " AND (full_name ILIKE %s OR donor_code ILIKE %s)"
            search_pattern = f'%{keyword}%'
            count_params.extend([search_pattern, search_pattern])
        
        if blood_type:
            count_query += " AND blood_type = %s"
            count_params.append(blood_type)
        
        if is_active:
            is_active_bool = is_active.lower() in ['true', '1', 'yes']
            count_query += " AND is_active = %s"
            count_params.append(is_active_bool)
        
        count_result = execute_query(count_query, tuple(count_params) if count_params else None, fetch_one=True)
        total_count = count_result['count'] if count_result else 0
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': donors,
            'count': total_count
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:donor_id>', methods=['GET'])
def get_donor(donor_id):
    """
    Get a single donor by ID with related information
    GET /api/donors/:id
    """
    try:
        query = """
            SELECT d.donor_id, d.donor_code, d.full_name, d.date_of_birth,
                   d.ethnicity, d.marital_status, d.blood_type, d.height_cm, d.weight_kg,
                   d.eye_color, d.hair_color, d.education_level, d.occupation,
                   d.contact_email, d.contact_phone, d.is_active, d.registration_date,
                   COUNT(DISTINCT sv.vial_id) as total_vials,
                   COUNT(DISTINCT ot.offspring_id) as total_offspring
            FROM donors d
            LEFT JOIN sperm_vials sv ON d.donor_id = sv.donor_id
            LEFT JOIN offspring_tracking ot ON d.donor_id = ot.donor_id
            WHERE d.donor_id = %s
            GROUP BY d.donor_id
        """
        
        donor = execute_query(query, (donor_id,), fetch_one=True)
        
        if not donor:
            return jsonify({
                'code': 404,
            'message': 'Donor not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': donor
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('', methods=['POST'])
def create_donor():
    """
    Create a new donor
    POST /api/donors
    """
    try:
        data = request.json
        
        # Validate required fields
        required_fields = ['donor_code', 'date_of_birth', 'blood_type']
        for field in required_fields:
            if field not in data:
                return jsonify({
                    'code': 400,
                    'message': f'Missing required field: {field}'
                }), 400
        
        query = """
            INSERT INTO donors (donor_code, full_name, date_of_birth, ethnicity,
                               marital_status, blood_type, height_cm, weight_kg,
                               eye_color, hair_color, education_level, occupation,
                               contact_email, contact_phone)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            RETURNING donor_id, donor_code, full_name, registration_date
        """
        
        params = (
            data['donor_code'],
            data.get('full_name'),
            data['date_of_birth'],
            data.get('ethnicity'),
            data.get('marital_status'),
            data['blood_type'],
            data.get('height_cm'),
            data.get('weight_kg'),
            data.get('eye_color'),
            data.get('hair_color'),
            data.get('education_level'),
            data.get('occupation'),
            data.get('contact_email'),
            data.get('contact_phone')
        )
        
        result = execute_query(query, params, fetch_one=True)
        donor_id = result['donor_id']
        
        # 如果前端请求自动创建医疗筛查，则创建7项标准筛查（默认全部通过）
        if data.get('create_screenings', False):
            from datetime import date
            today = date.today()
            
            # 标准筛查清单（7项，全部通过）
            screening_tests = [
                ('HIV', 'Negative', 'Central Lab'),
                ('Hepatitis B', 'Negative', 'Central Lab'),
                ('Hepatitis C', 'Negative', 'Central Lab'),
                ('Syphilis', 'Negative', 'Central Lab'),
                ('Genetic Screening', 'Pass', 'GenLab'),
                ('Chromosome Analysis', 'Normal', 'GenLab'),
                ('Semen Analysis', 'Good', 'Central Lab')
            ]
            
            screening_query = """
                INSERT INTO medical_screenings (donor_id, screening_date, test_name, result, lab_name, notes)
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            
            for test_name, result_value, lab_name in screening_tests:
                execute_query(screening_query, (donor_id, today, test_name, result_value, lab_name, ''), fetch=False)
        
        return jsonify({
            'code': 200,
            'message': 'Donor created successfully',
            'data': result
        }), 201
        
    except Exception as e:
        error_message = str(e)
        
        # 解析数据库约束错误，返回友好的错误信息
        if 'chk_donors_age' in error_message:
            return jsonify({
                'code': 400,
                'message': '捐精人年龄必须在18-40岁之间'
            }), 400
        elif 'chk_donors_blood_type' in error_message:
            return jsonify({
                'code': 400,
                'message': '血型格式不正确，必须是 A+, A-, B+, B-, AB+, AB-, O+, O- 之一'
            }), 400
        elif 'chk_donors_phone' in error_message:
            return jsonify({
                'code': 400,
                'message': '电话号码格式不正确：必须是"+"开头，后跟国家代码和7-15位号码（如 +8613800138000）'
            }), 400
        elif 'uq_donors_donor_code' in error_message:
            return jsonify({
                'code': 400,
                'message': '捐精人代码已存在'
            }), 400
        elif 'uq_donors_email' in error_message:
            return jsonify({
                'code': 400,
                'message': '该邮箱已被使用'
            }), 400
        elif 'uq_donors_phone' in error_message:
            return jsonify({
                'code': 400,
                'message': '该电话号码已被使用'
            }), 400
        elif 'chk_donors_height' in error_message:
            return jsonify({
                'code': 400,
                'message': 'Height must be between 150-210 cm'
            }), 400
        elif 'chk_donors_weight' in error_message:
            return jsonify({
                'code': 400,
                'message': 'Weight must be between 45-120 kg'
            }), 400
        else:
            return jsonify({
                'code': 500,
                'message': error_message
            }), 500

@bp.route('/<int:donor_id>', methods=['PUT'])
def update_donor(donor_id):
    """
    Update a donor
    PUT /api/donors/:id
    """
    try:
        data = request.json
        
        update_fields = []
        params = []
        
        # 核心字段不可修改：donor_code, date_of_birth, blood_type
        # 这些是身份/生物学核心特征，影响业务逻辑和受赠人选择
        allowed_fields = ['full_name', 'ethnicity', 'marital_status', 'height_cm',
                         'weight_kg', 'eye_color', 'hair_color', 'education_level',
                         'occupation', 'contact_email', 'contact_phone', 'is_active']
        
        for field in allowed_fields:
            if field in data:
                update_fields.append(f"{field} = %s")
                params.append(data[field])
        
        if not update_fields:
            return jsonify({
                'code': 400,
            'message': 'No fields to update'
        }), 400
        
        params.append(donor_id)
        
        query = f"""
            UPDATE donors
            SET {', '.join(update_fields)}
            WHERE donor_id = %s
            RETURNING donor_id, donor_code, full_name
        """
        
        result = execute_query(query, tuple(params), fetch_one=True)
        
        if not result:
            return jsonify({
                'code': 404,
            'message': 'Donor not found'
        }), 404
        
        return jsonify({
            'code': 200,
            'message': 'Donor updated successfully',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:donor_id>', methods=['DELETE'])
def delete_donor(donor_id):
    """
    Delete a donor (hard delete - permanently remove from database)
    DELETE /api/donors/:id
    """
    try:
        # 先查询捐精人是否存在
        check_query = """
            SELECT donor_id, donor_code, full_name
            FROM donors
            WHERE donor_id = %s
        """
        donor = execute_query(check_query, (donor_id,), fetch_one=True)
        
        if not donor:
            return jsonify({
                'code': 404,
                'message': 'Donor not found'
            }), 404
        
        # 检查是否有后代记录
        offspring_query = """
            SELECT COUNT(*) as count
            FROM offspring_tracking
            WHERE donor_id = %s
        """
        offspring_result = execute_query(offspring_query, (donor_id,), fetch_one=True)
        
        if offspring_result and offspring_result['count'] > 0:
            return jsonify({
                'code': 400,
                'message': f'无法删除：该捐精人有 {offspring_result["count"]} 条后代记录。出于法律和伦理原因，有后代记录的捐精人不可删除。如需停用，请将状态设为"非活跃"。'
            }), 400
        
        # 检查是否有样本被订单使用
        vial_usage_query = """
            SELECT COUNT(*) as count
            FROM sperm_vials sv
            JOIN order_items oi ON sv.vial_id = oi.vial_id
            WHERE sv.donor_id = %s
        """
        vial_usage_result = execute_query(vial_usage_query, (donor_id,), fetch_one=True)
        
        if vial_usage_result and vial_usage_result['count'] > 0:
            return jsonify({
                'code': 400,
                'message': f'无法删除：该捐精人的样本已被订单使用。为保留订单历史记录，不可删除。如需停用，请将状态设为"非活跃"。'
            }), 400
        
        # 物理删除（由于外键有 ON DELETE CASCADE，相关的样本、体检记录等会自动删除）
        delete_query = """
            DELETE FROM donors
            WHERE donor_id = %s
        """
        execute_query(delete_query, (donor_id,), fetch=False)
        
        return jsonify({
            'code': 200,
            'message': 'Donor deleted successfully',
            'data': donor
        }), 200
        
    except Exception as e:
        error_message = str(e)
        # 检查是否是外键约束错误
        if 'foreign key' in error_message.lower() or 'violates' in error_message.lower():
            return jsonify({
                'code': 400,
                'message': '无法删除：该捐精人存在关联数据（如后代记录、订单记录等）。如需停用，请将状态设为"非活跃"。'
            }), 400
        return jsonify({
            'code': 500,
            'message': error_message
        }), 500

@bp.route('/<int:donor_id>/vials', methods=['GET'])
def get_donor_vials(donor_id):
    """
    Get all vials for a specific donor
    GET /api/donors/:id/vials
    """
    try:
        query = """
            SELECT vial_id, collection_date, motility_percent, sperm_count_mil,
                   volume_ml, status, storage_location, quarantine_release_date
            FROM sperm_vials
            WHERE donor_id = %s
            ORDER BY collection_date ASC
        """
        
        vials = execute_query(query, (donor_id,))
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': vials,
            'count': len(vials)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:donor_id>/offspring_count', methods=['GET'])
def get_donor_offspring_count(donor_id):
    """
    Get offspring count for a specific donor
    GET /api/donors/:id/offspring_count
    """
    try:
        query = """
            SELECT COUNT(*) as count
            FROM offspring_tracking
            WHERE donor_id = %s
        """
        
        result = execute_query(query, (donor_id,), fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': result
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/<int:donor_id>/screenings', methods=['GET'])
def get_donor_screenings(donor_id):
    """
    Get all medical screenings for a specific donor
    GET /api/donors/:id/screenings
    """
    try:
        query = """
            SELECT screening_id, screening_date, test_name, result, lab_name, notes
            FROM medical_screenings
            WHERE donor_id = %s
            ORDER BY screening_date ASC
        """
        
        screenings = execute_query(query, (donor_id,))
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': screenings,
            'count': len(screenings)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/search', methods=['GET'])
def search_donors():
    """
    Search donors by name, donor_code, or blood_type
    GET /api/donors/search?q=xxx
    """
    try:
        search_term = request.args.get('q', '')
        
        if not search_term:
            return jsonify({
                'code': 400,
            'message': 'Search term is required'
        }), 400
        
        query = """
            SELECT donor_id, donor_code, full_name, blood_type, age(date_of_birth) as age,
                   ethnicity, education_level, is_active
            FROM donors
            WHERE (full_name ILIKE %s OR donor_code ILIKE %s OR blood_type ILIKE %s)
            AND is_active = true
            ORDER BY donor_code
        """
        
        search_pattern = f'%{search_term}%'
        donors = execute_query(query, (search_pattern, search_pattern, search_pattern))
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': donors,
            'count': len(donors)
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

@bp.route('/stats', methods=['GET'])
def get_donor_stats():
    """
    Get donor statistics
    GET /api/donors/stats
    """
    try:
        query = """
            SELECT 
                COUNT(*) as total_donors,
                COUNT(*) FILTER (WHERE is_active = true) as active_donors,
                COUNT(DISTINCT blood_type) as blood_types,
                AVG(height_cm) as avg_height,
                AVG(weight_kg) as avg_weight
            FROM donors
        """
        
        stats = execute_query(query, fetch_one=True)
        
        return jsonify({
            'code': 200,
            'message': 'success',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({
            'code': 500,
            'message': str(e)
        }), 500

